package com.ericdm.callingandsmsstatistics;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseOperator
{	
	public static final String DATA_TYPE_OUTGOING_CALL = "outGoingCall";
	public static final String DATA_TYPE_INCOMIG_CALL = "incomingCall";
	public static final String DATA_TYPE_SENDED_SMS = "sendedSMS";
	public static final String DATA_TYPE_RECEVIED_SMS = "receviedSMS";
	
	public static final String TABLE_NAME_OUGGOINGCALL = "CallingAndSmsHistoryTableOUTGOINGCALL";
	public static final String TABLE_NAME_INCOMINGCALL = "CallingAndSmsHistoryTableINCOMINGCALL";
	public static final String TABLE_NAME_RECEVIEDSMS = "CallingAndSmsHistoryTableRECEVIEDSMS";
	public static final String TABLE_NAME_SENDEDSMS = "CallingAndSmsHistoryTableSENDEDSMS";
	
	ContentValues values = new ContentValues();
	
	public void insertData(String dataType, String data, String phoneNumber, String date_, String fullDate)
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("PHONENUMBER", phoneNumber);
		values.put("DATE_", date_);
		values.put("DATEFULL", fullDate);
		if (dataType.equals(DATA_TYPE_INCOMIG_CALL))
		{
			values.put("INCOMMING_CALL", data);
			sqLiteDatabase.insert("CallingAndSmsHistoryTableINCOMINGCALL", null, values);
		}
		else if (dataType.equals(DATA_TYPE_OUTGOING_CALL)) 
		{
			values.put("OUT_GOING_CALL", data);
			sqLiteDatabase.insert("CallingAndSmsHistoryTableOUTGOINGCALL", null, values);
		}
		else if (dataType.equals(DATA_TYPE_RECEVIED_SMS)) 
		{
			values.put("RECEVIED_SMS", data);
			sqLiteDatabase.insert("CallingAndSmsHistoryTableRECEVIEDSMS", null, values);
		}
		else if (dataType.equals(DATA_TYPE_SENDED_SMS)) 
		{
			values.put("SENDED_SMS", data);
			sqLiteDatabase.insert("CallingAndSmsHistoryTableSENDEDSMS", null, values);
		}
		sqLiteDatabase.close();	
	}
	
	public void insertSumIncomingTime(int sumIcoming) 
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("SUM_INCOMING", sumIcoming);
		int row = sqLiteDatabase.update("CallingAndSmsHistoryTableSUMINCOMING", values, null, null);
		if (row == 0)  
		{
			sqLiteDatabase.insert("CallingAndSmsHistoryTableSUMINCOMING", null, values);
		}
		sqLiteDatabase.close();	
	}
	
	public void insertSumOutgoingTime(int time) 
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("SUM_OUTGOING", time);
		int row = sqLiteDatabase.update("CallingAndSmsHistoryTableSUMOUTGOING", values, null, null);
		if (row == 0) 
		{
			sqLiteDatabase.insert("CallingAndSmsHistoryTableSUMOUTGOING", null, values);
		}
		sqLiteDatabase.close();	
	}
	
	public void insertSumSendSms(int time) 
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("SUM_SENDSMS", time);
		int row = sqLiteDatabase.update("CallingAndSmsHistoryTableSUMSENDSMS", values, null, null);
		if (row == 0) 
		{
			sqLiteDatabase.insert("CallingAndSmsHistoryTableSUMSENDSMS", null, values);
		}
		sqLiteDatabase.close();	
	}	
	
	public void insertSumReceiveSms(int time) 
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("SUM_RECEIVESMS", time);
		int row = sqLiteDatabase.update("CallingAndSmsHistoryTableSUMRECEIVESMS", values, null, null);
		if (row == 0) 
		{
			sqLiteDatabase.insert("CallingAndSmsHistoryTableSUMRECEIVESMS", null, values);
		}
		sqLiteDatabase.close();	
	}	
	
	public int getPhoneNumberIncmingCallTime(String pn) {
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		String phone;
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTablePHONENUMBERINCOMING", new String[] { "PHONENUMBER", "PHONEINCOMINGTIME"}, null, null, null, null, null);
		while (cursor.moveToNext())  
		{
			phone = cursor.getString(cursor.getColumnIndex("PHONENUMBER")); 
			if (phone == null) 
			{
				cursor.close();
				sqLiteDatabase.close();
				return 0;  
			}
			if (pn.equals(phone))
			{
				return cursor.getInt(cursor.getColumnIndex("PHONEINCOMINGTIME")); 
			}				
		}
		sqLiteDatabase.close();
		cursor.close();
		return 0;
	}
	
	public int getPhoneNumberOutgoingCallTime(String pn) {
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		String phone;
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTablePHONENUMBEROUTGOING", new String[] { "PHONENUMBER", "PHONEOUTGOINGTIME"}, null, null, null, null, null);
		while (cursor.moveToNext())  
		{
			phone = cursor.getString(cursor.getColumnIndex("PHONENUMBER")); 
			if (phone == null) 
			{
				cursor.close();
				sqLiteDatabase.close();
				return 0;  
			}
			if (pn.equals(phone))
			{
				return cursor.getInt(cursor.getColumnIndex("PHONEOUTGOINGTIME")); 
			}				
		}
		sqLiteDatabase.close();
		cursor.close();
		return 0;
	}	
	
	public int getPhoneNumberSendSms(String pn) {
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		String phone;
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTablePHONENUMBERSENDSMS", new String[] { "PHONENUMBER", "PHONESENDSMS"}, null, null, null, null, null);
		while (cursor.moveToNext())  
		{
			phone = cursor.getString(cursor.getColumnIndex("PHONENUMBER")); 
			if (phone == null) 
			{
				cursor.close();
				sqLiteDatabase.close();
				return 0;  
			}
			if (pn.equals(phone))
			{
				return cursor.getInt(cursor.getColumnIndex("PHONESENDSMS")); 
			}				
		}
		sqLiteDatabase.close();
		cursor.close();
		return 0;
	}	
	
	public int getPhoneNumberRecevieSms(String pn) {
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		String phone;
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTablePHONENUMBERRECIVESMS", new String[] { "PHONENUMBER", "PHONERECEIVESMS"}, null, null, null, null, null);
		while (cursor.moveToNext())  
		{
			phone = cursor.getString(cursor.getColumnIndex("PHONENUMBER")); 
			if (phone == null) 
			{
				cursor.close();
				sqLiteDatabase.close();
				return 0;  
			}
			if (pn.equals(phone))
			{
				return cursor.getInt(cursor.getColumnIndex("PHONERECEIVESMS")); 
			}				
		}
		sqLiteDatabase.close();
		cursor.close();
		return 0;
	}		
	
	public void setPhoneNumberIncmingCallTime(String pn, int time) {
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("PHONENUMBER", pn);
		values.put("PHONEINCOMINGTIME", time);
		String phone;
		Boolean isHasRecord = false;
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTablePHONENUMBERINCOMING", new String[] { "PHONENUMBER"}, null, null, null, null, null);
		while (cursor.moveToNext())  
		{
			phone = cursor.getString(cursor.getColumnIndex("PHONENUMBER")); 
			if (phone == null) 
			{
				cursor.close();
				isHasRecord = false;
			}
			if (pn.equals(phone))
			{
				isHasRecord = true;
				break;
			}		
		}
		if (isHasRecord){
			sqLiteDatabase.delete("CallingAndSmsHistoryTablePHONENUMBERINCOMING", "PHONENUMBER=?",  new String[] { pn});
		}
		sqLiteDatabase.insert("CallingAndSmsHistoryTablePHONENUMBERINCOMING", null, values);
		sqLiteDatabase.close();	
		cursor.close();
	}
	
	public void setPhoneNumberOutgoingCallTime(String pn, int time) {
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("PHONENUMBER", pn);
		values.put("PHONEOUTGOINGTIME", time);
		String phone;
		Boolean isHasRecord = false;
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTablePHONENUMBEROUTGOING", new String[] { "PHONENUMBER"}, null, null, null, null, null);
		while (cursor.moveToNext())  
		{
			phone = cursor.getString(cursor.getColumnIndex("PHONENUMBER")); 
			if (phone == null) 
			{
				cursor.close();
				isHasRecord = false;
			}
			if (pn.equals(phone))
			{
				isHasRecord = true;
				break;
			}		
		}
		if (isHasRecord){
			sqLiteDatabase.delete("CallingAndSmsHistoryTablePHONENUMBEROUTGOING", "PHONENUMBER=?",  new String[] { pn});
		}
		sqLiteDatabase.insert("CallingAndSmsHistoryTablePHONENUMBEROUTGOING", null, values);
		sqLiteDatabase.close();	
		cursor.close();
	}
	
	public void setPhoneNumberSendSms(String pn, int time) {
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("PHONENUMBER", pn);
		values.put("PHONESENDSMS", time);
		String phone;
		Boolean isHasRecord = false;
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTablePHONENUMBERSENDSMS", new String[] { "PHONENUMBER"}, null, null, null, null, null);
		while (cursor.moveToNext())  
		{
			phone = cursor.getString(cursor.getColumnIndex("PHONENUMBER")); 
			if (phone == null) 
			{
				cursor.close();
				isHasRecord = false;
			}
			if (pn.equals(phone))
			{
				isHasRecord = true;
				break;
			}		
		}
		if (isHasRecord){
			sqLiteDatabase.delete("CallingAndSmsHistoryTablePHONENUMBERSENDSMS", "PHONENUMBER=?",  new String[] { pn});
		}
		sqLiteDatabase.insert("CallingAndSmsHistoryTablePHONENUMBERSENDSMS", null, values);
		sqLiteDatabase.close();	
		cursor.close();
	}	
	
	public void setPhoneNumberReceiveSms(String pn, int time) {
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		values.clear();
		values.put("PHONENUMBER", pn);
		values.put("PHONERECEIVESMS", time);
		String phone;
		Boolean isHasRecord = false;
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTablePHONENUMBERRECIVESMS", new String[] { "PHONENUMBER"}, null, null, null, null, null);
		while (cursor.moveToNext())  
		{
			phone = cursor.getString(cursor.getColumnIndex("PHONENUMBER")); 
			if (phone == null) 
			{
				cursor.close();
				isHasRecord = false;
			}
			if (pn.equals(phone))
			{
				isHasRecord = true;
				break;
			}		
		}
		if (isHasRecord){
			sqLiteDatabase.delete("CallingAndSmsHistoryTablePHONENUMBERRECIVESMS", "PHONENUMBER=?",  new String[] { pn});
		}
		sqLiteDatabase.insert("CallingAndSmsHistoryTablePHONENUMBERRECIVESMS", null, values);
		sqLiteDatabase.close();	
		cursor.close();
	}		
	
	public void deleteRecord(String tablename, String phoneNumber)
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		sqLiteDatabase.delete(tablename, "PHONENUMBER=?", new String[] {phoneNumber});
		sqLiteDatabase.close();
	}
	
	public void insertQueryTime(String stratTime, String endTime)
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		sqLiteDatabase.delete("CallingAndSmsHistoryTableQUERYTIME", "STARTTIME", null);
		ContentValues values = new ContentValues();
		values.clear();
		values.put("STARTTIME", stratTime);
		values.put("ENDTIME", endTime);
		sqLiteDatabase.insert("CallingAndSmsHistoryTableQUERYTIME", null, values);
		sqLiteDatabase.close();
	}
	
	public int GetSumIncomingCall()
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableSUMINCOMING", new String[] { "SUM_INCOMING" }, null, null, null, null, null);
		int Sum = 0;
		while (cursor.moveToNext()) 
		{
			Sum = cursor.getInt(cursor.getColumnIndex("SUM_INCOMING")); 
		}
		cursor.close();
		sqLiteDatabase.close();
		return Sum;
	}
	
	public int GetSumOutgoingCall()
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableSUMOUTGOING", new String[] { "SUM_OUTGOING"}, null, null, null, null, null);
		int Sum = 0;
		while (cursor.moveToNext()) 
		{
			Sum = cursor.getInt(cursor.getColumnIndex("SUM_OUTGOING")); 
		}
		cursor.close();
		sqLiteDatabase.close();
		return Sum;
	}	
	
	public int GetSumReceiveSMS()
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableSUMRECEIVESMS", new String[] { "SUM_RECEIVESMS"}, null, null, null, null, null);
		int Sum = 0;
		while (cursor.moveToNext()) 
		{
			Sum = cursor.getInt(cursor.getColumnIndex("SUM_RECEIVESMS")); 
		}
		cursor.close();
		sqLiteDatabase.close();
		return Sum;
	}	
	
	public int GetSumSendSMS()
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableSUMSENDSMS", new String[] { "SUM_SENDSMS"}, null, null, null, null, null);
		int Sum = 0;
		while (cursor.moveToNext()) 
		{
			Sum = cursor.getInt(cursor.getColumnIndex("SUM_SENDSMS")); 
		}
		cursor.close();
		sqLiteDatabase.close();
		return Sum;
	}		

	public String QueryTimeStrat()
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableQUERYTIME", new String[] { "STARTTIME"}, null, null, null, null, null);
		String date = "";
		while (cursor.moveToNext()) 
		{
			date = cursor.getString(cursor.getColumnIndex("STARTTIME")); 
			if (date == null) 
			{
				cursor.close();
				sqLiteDatabase.close();
				return "";  
			}
			cursor.close();
			sqLiteDatabase.close();
			return date;
		}
		cursor.close();
		sqLiteDatabase.close();
		return "";
	}
	
	public String QueryTimeEnd()
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableQUERYTIME", new String[] { "ENDTIME"}, null, null, null, null, null);
		String date = "";
		while (cursor.moveToNext()) 
		{
			date = cursor.getString(cursor.getColumnIndex("ENDTIME")); 
			if (date == null) 
			{
				cursor.close();
				sqLiteDatabase.close();
				return "";  
			}
			cursor.close();
			sqLiteDatabase.close();
			return date;
		}
		cursor.close();
		sqLiteDatabase.close();
		return "";
	}
	
	public String getData(String dataType, String phoneNumber, String beginDate, String endDate)
	{
		DatabaseHelper databaseHelper = new DatabaseHelper(MainApplication.getContext(), "CallingHistoryDatabase");
		SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
		String outGoingCall = "";
		String inComingCall = "";
		String sendedSms = "";
		String receviedSms = "";
		int statisticsData = 0;
		String date;
		if (dataType.equals(DATA_TYPE_INCOMIG_CALL))
		{
			Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableINCOMINGCALL", new String[] { "PHONENUMBER", "INCOMMING_CALL", "DATE_"}, "PHONENUMBER=?", new String[] { phoneNumber }, null, null, null);
			while (cursor.moveToNext())  
			{
				date = cursor.getString(cursor.getColumnIndex("DATE_")); 
				if (date == null) 
				{
					cursor.close();
					sqLiteDatabase.close();
					return "";  
				}
				if ((date.compareTo(beginDate) >= 0) && (date.compareTo(endDate) <= 0))
				{
					inComingCall = cursor.getString(cursor.getColumnIndex("INCOMMING_CALL")); 
					int currentData = 0;
					currentData = Integer.parseInt(inComingCall);
					statisticsData = statisticsData + currentData;
				}				
			}
			sqLiteDatabase.close();
			cursor.close();
			return statisticsData + "";
		}
		else if (dataType.equals(DATA_TYPE_OUTGOING_CALL)) 
		{
			Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableOUTGOINGCALL", new String[] { "PHONENUMBER", "OUT_GOING_CALL", "DATE_"}, "PHONENUMBER=?", new String[] { phoneNumber }, null, null, null);
			while (cursor.moveToNext())  
			{
				date = cursor.getString(cursor.getColumnIndex("DATE_")); 
				if (date == null) 
				{
					sqLiteDatabase.close();
					cursor.close();
					return "";  
				}
				if ((date.compareTo(beginDate) >= 0) && (date.compareTo(endDate) <= 0))
				{
					outGoingCall = cursor.getString(cursor.getColumnIndex("OUT_GOING_CALL")); 
					int currentData = 0;
					currentData = Integer.parseInt(outGoingCall);
					statisticsData = statisticsData + currentData;
				}				
			}
			sqLiteDatabase.close();
			cursor.close();
			return statisticsData + "";
		}
		else if (dataType.equals(DATA_TYPE_RECEVIED_SMS)) 
		{
			Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableRECEVIEDSMS", new String[] { "PHONENUMBER", "RECEVIED_SMS", "DATE_"}, "PHONENUMBER=?", new String[] { phoneNumber }, null, null, null);
			while (cursor.moveToNext())  
			{
				date = cursor.getString(cursor.getColumnIndex("DATE_")); 
				if (date == null) 
				{
					sqLiteDatabase.close();
					cursor.close();
					return "";  
				}
				if ((date.compareTo(beginDate) >= 0) && (date.compareTo(endDate) <= 0))
				{
					receviedSms = cursor.getString(cursor.getColumnIndex("RECEVIED_SMS")); 
					int currentData = 0;
					currentData = Integer.parseInt(receviedSms);
					statisticsData = statisticsData + currentData;
				}				
			}
			sqLiteDatabase.close();
			cursor.close();
			return statisticsData + "";
		}
		else if (dataType.equals(DATA_TYPE_SENDED_SMS)) 
		{
			Cursor cursor = sqLiteDatabase.query("CallingAndSmsHistoryTableSENDEDSMS", new String[] { "PHONENUMBER", "SENDED_SMS", "DATE_"}, "PHONENUMBER=?", new String[] { phoneNumber }, null, null, null);
			while (cursor.moveToNext())  
			{
				date = cursor.getString(cursor.getColumnIndex("DATE_")); 
				if (date == null) 
				{
					sqLiteDatabase.close();
					cursor.close();
					return "";  
				}
				if ((date.compareTo(beginDate) >= 0) && (date.compareTo(endDate) <= 0))
				{
					sendedSms = cursor.getString(cursor.getColumnIndex("SENDED_SMS")); 
					int currentData = 0;
					currentData = Integer.parseInt(sendedSms);
					statisticsData = statisticsData + currentData;
				}				
			}
			sqLiteDatabase.close();
			cursor.close();
			return statisticsData + "";
		}
		sqLiteDatabase.close();
		return "";
	}
}
